/**
 * 
 */
package org.gocom.components.conframe.util;

import java.io.FileOutputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;

import com.eos.common.connection.SimpleConnectionHelper;
import com.eos.system.annotation.Bizlet;
import com.primeton.das.entity.impl.hibernate.sql.Delete;

/**
 * @author jesse
 * @date 2016-09-18 16:58:14
 *
 */
@Bizlet("")
public class HSSFWorkUtil {
	
	public static void main(String[] args) throws Exception {
		try {
			OutputStream out = new FileOutputStream("D:\\test.xls");
			
			/*String []  data = new String[5];
			for (int i = 0; i < 5; i++) {
				String st=i+"&东霖柏鸿";
				data[i]=st;
			}
			String[] headers = { "ID", "用户名" };
			
			exportExcel(workbook, 0, "上海", headers, data);
			exportExcel(workbook, 1, "深圳", headers, data);
			exportExcel(workbook, 2, "广州", headers, data);*/
			//原理就是将所有的数据一起写入，然后再关闭输入流。
			List<String[]>  result=new ArrayList<String[]>();
			String [] headers=new String[3];
			headers[0]="code";
			headers[1]="name";
			headers[2]="sex";
			
			String[] data1 = new String[3];
			for(int i=0;i<3;i++){
				data1[0]=i+"";
				data1[1]="李狗蛋"+i;
				data1[2]="男";
				result.add(data1);
			}
			
			
			HSSFWorkbook workbook =exportByDataGrid(null, headers, result);
			workbook.write(out);
			out.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	@Bizlet("")
	public static String[] praseTitle(){
		String [] headers=new String[3];
		headers[0]="code";
		headers[1]="name";
		headers[2]="sex";
		return headers;
		
	}
	
	@Bizlet("")
	public static List<String[]> praseData(){
		List<String[]>  result=new ArrayList<String[]>();
		String [] headers=new String[3];
		headers[0]="code";
		headers[1]="name";
		headers[2]="sex";
		
		String[] data1 = new String[3];
		for(int i=0;i<3;i++){
			data1[0]=i+"";
			data1[1]="李狗蛋"+i;
			data1[2]="男";
			result.add(data1);
		}
		return result;
	}
	
	
	/**
	 * 单个namesql导出EXCEL
	 * @param path 导出路径
	 * @param sheetTitle  sheet名字
	 * @param headers  头
	 * @param sql  查询语句
	 */
	@Bizlet("exportPathByNameSql")
	public static void exportPathByNameSql(String path,String sheetTitle,String[] headers,String sql,String datasource)throws Exception {
		Connection conn = null;
		PreparedStatement preStatement = null;
		ResultSet resultSet = null;
		OutputStream out = new FileOutputStream(path);
		HSSFWorkbook workbook = new HSSFWorkbook();
		
		try {
			//取系统的数据库连接
			conn = SimpleConnectionHelper.getConnection(datasource);
			
			preStatement = conn.prepareStatement(sql.toString());
			resultSet = preStatement.executeQuery();
			exportExcelForResultSet(workbook,sheetTitle, headers, resultSet);
		} catch (Exception e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		}finally{
			preStatement.close();
			conn.close();
		}
		workbook.write(out);
		out.close();
	}
	
	/**
	 * 根据组装的 HSSFWorkBean导出EXCEL
	 * @param HSSFWorkBean
	 * @throws Exception
	 */
	@Bizlet("exportPathByHSSFWorkBean")
	public static void exportPathByHSSFWorkBean(HSSFWorkBean HSSFWorkBean)throws Exception{ 
		exportPathByNameSqlList(HSSFWorkBean.getPath(), HSSFWorkBean.getSheetTitles(), HSSFWorkBean.getHeaders(), HSSFWorkBean.getSqls(), HSSFWorkBean.getDatasources());
	}
	
	
	/**
	 * 多namesql的excel文件导出
	 * @param path  保存路径
	 * @param sheetTitle
	 * @param headers
	 * @param sql
	 * @param datasource
	 * @throws Exception
	 */
	@Bizlet("exportPathByNameSqlList")
	public static void exportPathByNameSqlList(String path,String[] sheetTitles,String[] headers,String[] sqls,String[] datasources)throws Exception {
		
		HSSFWorkbook workbook = new HSSFWorkbook();
		for(int i=0;i<sheetTitles.length;i++){
			Connection conn = null;
			PreparedStatement preStatement = null;
			ResultSet resultSet = null;
			try {
				//取系统的数据库连接
				conn = SimpleConnectionHelper.getConnection(datasources[i]);
				preStatement = conn.prepareStatement(sqls[i].toString());
				resultSet = preStatement.executeQuery();
				exportExcelForResultSet(workbook,sheetTitles[i], headers, resultSet);
			} catch (Exception e) {
				// TODO 自动生成的 catch 块
				e.printStackTrace();
			}finally{
				preStatement.close();
				conn.close();
			}
		}
		OutputStream out = new FileOutputStream(path);
		workbook.write(out);
		out.close();
	}
	
	
	/**
	 * 多namesql的excel文件导出
	 * @param path  保存路径
	 * @param tablename 表名称
	 * @param headers
	 * @param sqls sql语句
	 * @param datasource
	 * @throws Exception
	 */
	@Bizlet("exportByNameSql")
	public static void exportByNameSql(String path,String[] tablename,String[] sql,String datasource)throws Exception {
		
		HSSFWorkbook workbook = new HSSFWorkbook();
		for(int i=0;i<tablename.length;i++){
			String sheetTitles=null;//excelsheet名
			String[] headers=null;//第一行
			Connection conn = null;//链接
			PreparedStatement preStatement = null;//直通车
			ResultSet resultSet = null;//返回信息
			String[] keyid = null;//主键中转列表
			try {
				//取系统的数据库连接
				conn = SimpleConnectionHelper.getConnection(datasource);
				preStatement = conn.prepareStatement(sql[i]);
				resultSet = preStatement.executeQuery();
				ResultSetMetaData rsd = resultSet.getMetaData(); 
								headers=new String[rsd.getColumnCount()];
								//excelsheet名
								sheetTitles=new String(tablename[i]);
								//获取主键
								DatabaseMetaData dbMeta = conn.getMetaData(); 
								ResultSet pkRSet = dbMeta.getPrimaryKeys(null, null, tablename[i]);
								  //ResultSetMetaData rsmd = pkRSet.getMetaData(); 
								keyid=new String[rsd.getColumnCount()];
								int b=0;
							      while(pkRSet.next()) {  
							    	  	keyid[b]=pkRSet.getString(4);//主键中转存放
							            b++;
							      }
							    String[] keyidid=new String[b];//定义真正主键数组个数
							    for(int c=0;c<b;c++){
							    	keyidid[c]=keyid[c];
							    }
							    String key="KEY";//主键前面加KEY:
							    String columnName=null;//列名
							    String datecolum="DATE";//字段为日期
							    String timecolum="DATETIME";//字段为日期加时间
							    String time="hh:mm:ss";//时间格式
							    String datetime="yyyy-MM-dd";//日期格式
						 for(int j = 0; j < rsd.getColumnCount(); j++) { 
							 	datetime="yyyy-MM-dd";//重新定义
							 	columnName=rsd.getColumnName(j + 1);
							 	for(int t=0;t<keyidid.length;t++){
							 		if(keyidid[t].equals(columnName)){
							 			columnName=key+':'+columnName;
							 		}
							 	}
							 	if(rsd.getColumnTypeName(j + 1).equals(datecolum)) datetime=rsd.getColumnTypeName(j + 1)+':'+datetime;
							 	else if(rsd.getColumnTypeName(j + 1).equals(timecolum)) datetime=rsd.getColumnTypeName(j + 1)+':'+datetime+' '+time;
							 	else datetime=rsd.getColumnTypeName(j + 1);
								headers[j]=columnName+':'+datetime+':'+rsd.getColumnDisplaySize(j + 1);
						        /*System.out.print("java类型："+rsd.getColumnClassName(j + 1));  
						        System.out.print("  数据库类型:"+rsd.getColumnTypeName(j + 1));  
						        System.out.print("  字段名称:"+rsd.getColumnName(j + 1));  
						        System.out.print("  字段长度:"+rsd.getColumnDisplaySize(j + 1));  
						        System.out.println();  */
						      }
				exportExcelForResultSet(workbook,sheetTitles, headers, resultSet);
			} catch (Exception e) {
				// TODO 自动生成的 catch 块
				e.printStackTrace();
			}finally{
				preStatement.close();
				conn.close();
			}
		}
		OutputStream out = new FileOutputStream(path);
		workbook.write(out);
		out.close();
	}
	
	
	/**
	 * 
	 * @param path  文件下载路径
	 * @param sheetTitle 文件sheetname
	 * @param headers 标题抬头
	 * @param result 参数值
	 */
	@Bizlet("")
	public  static void exportPath(String path,String sheetTitle, String[] headers, List<String[]>  result){
		try {
			OutputStream out = new FileOutputStream(path);
			HSSFWorkbook workbook =exportByDataGrid(sheetTitle, headers, result);
			workbook.write(out);
			out.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 直接根据结果数据导出excel
	 * @param workbook HSSFWorkbook对象
	 * @param sheetTitle  sheet名
	 * @param headers  
	 * @param result
	 * @throws Exception
	 */
	@Bizlet("")
	public static HSSFWorkbook exportByDataGrid( String sheetTitle, String[] headers, List<String[]>  result) throws Exception{
		HSSFWorkbook workbook = new HSSFWorkbook();
		if(StringUtils.isEmpty(sheetTitle)){
			sheetTitle="sheet1";
		}
		//String [] st=new String[result.size()];
		//for(int i=0;i< result.size();i++){
		//	String [] arr=(String[]) result.get(i);
		//	StringBuffer sb=new StringBuffer();
		//	for(int j=0;j<arr.length;j++){
		//		String co=arr[j];
		//		sb.append(co+"&");
		//	}
		//	String str=sb.toString();
		//	str=str.substring(0, str.length()-1);
		//	st[i]=sb.toString();
		//}
		
		//exportExcel(workbook, 0,sheetTitle, headers, st);
		// 生成一个表格
		HSSFSheet sheet = workbook.createSheet();
		workbook.setSheetName(0, sheetTitle);
		// 设置表格默认列宽度为20个字节
		sheet.setDefaultColumnWidth((short) 20);
		// 生成一个样式
		HSSFCellStyle style = workbook.createCellStyle();
		// 设置这些样式
		style.setFillForegroundColor(HSSFColor.PALE_BLUE.index);
		style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);
		style.setBorderTop(HSSFCellStyle.BORDER_THIN);
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		// 生成一个字体
		HSSFFont font = workbook.createFont();
		font.setColor(HSSFColor.BLACK.index);
		font.setFontHeightInPoints((short) 12);
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		// 把字体应用到当前的样式
		style.setFont(font);

		// 指定当单元格内容显示不下时自动换行
		style.setWrapText(true);

		// 产生表格标题行
		HSSFRow row = sheet.createRow(0);
		for (int i = 0; i < headers.length; i++) {
			HSSFCell cell = row.createCell((short) i);
			cell.setCellStyle(style);
			HSSFRichTextString text = new HSSFRichTextString(headers[i]);
			cell.setCellValue(text.toString());
		}
		// 遍历集合数据，产生数据行
		if (result != null) {
			int index = 1;
			for (String[] stm : result) {
				row = sheet.createRow(index);
				int cellIndex = 0;
				for (String str : stm) {
					HSSFCell cell = row.createCell((short) cellIndex);
					if(StringUtils.isBlank(str)){
						str="";
					}
					cell.setCellValue(str.toString());
					cellIndex++;
				}
				index++;
			}
		}
		return workbook;
	}
	

	
	@Bizlet("")
	public static void exportData(String filename,HttpServletRequest request,HttpServletResponse response,HSSFWorkbook workbook, int sheetNum,
			String sheetTitle, String[] headers,String [] result){
		try {
			String fileName = filename+".xls";
			if(request.getHeader("USER-AGENT").toLowerCase().indexOf("firefox") > 0){// FF
			    fileName = new String(fileName.getBytes("UTF-8"), "iso-8859-1");  
			}else{// Other
				fileName = URLEncoder.encode(fileName, "UTF-8");  
			}
			response.setContentType("application/octet-stream; charset=utf-8");     
			response.setHeader("Content-disposition", "attachment;filename=" + fileName);     
			OutputStream out = response.getOutputStream();
			
			exportExcel(workbook, sheetNum, sheetTitle, headers, result);
			workbook.write(out);
			out.flush();
			out.close();
		} catch (Exception e) {
			
		} 
	}
	
	/**
	 * @Title: exportExcel
	 * @Description: 导出Excel的方法
	 * @author: jesse @ 2015-09-29
	 * @param workbook 
	 * @param sheetNum (sheet的位置，0表示第一个表格中的第一个sheet)
	 * @param sheetTitle  （sheet的名称）
	 * @param headers    （表格的标题）
	 * @param result   （表格的数据）字符串数组，里面也是一个字符串数组,里面的字符串用&隔开
	 * @param out  （输出流）
	 * @throws Exception
	 */
	@Bizlet("")
	public static void exportExcel(HSSFWorkbook workbook, int sheetNum,
			String sheetTitle, String[] headers, String [] result) throws Exception {
		// 生成一个表格
		HSSFSheet sheet = workbook.createSheet();
		workbook.setSheetName(sheetNum, sheetTitle);
		// 设置表格默认列宽度为20个字节
		sheet.setDefaultColumnWidth((short) 20);
		// 生成一个样式
		HSSFCellStyle style = workbook.createCellStyle();
		// 设置这些样式
		style.setFillForegroundColor(HSSFColor.PALE_BLUE.index);
		style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);
		style.setBorderTop(HSSFCellStyle.BORDER_THIN);
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		// 生成一个字体
		HSSFFont font = workbook.createFont();
		font.setColor(HSSFColor.BLACK.index);
		font.setFontHeightInPoints((short) 12);
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		// 把字体应用到当前的样式
		style.setFont(font);

		// 指定当单元格内容显示不下时自动换行
		style.setWrapText(true);

		// 产生表格标题行
		HSSFRow row = sheet.createRow(0);
		for (int i = 0; i < headers.length; i++) {
			HSSFCell cell = row.createCell((short) i);
			cell.setCellStyle(style);
			HSSFRichTextString text = new HSSFRichTextString(headers[i]);
			cell.setCellValue(text.toString());
		}
		// 遍历集合数据，产生数据行
		if (result != null) {
			int index = 1;
			for (String m : result) {
				if(StringUtils.isNotEmpty(m)){
				row = sheet.createRow(index);
				int cellIndex = 0;
				String [] stm=m.split("&");
				for (String str : stm) {
					HSSFCell cell = row.createCell((short) cellIndex);
					if(StringUtils.isBlank(str)){
						str="";
					}
					cell.setCellValue(str.toString());
					cellIndex++;
				}
				index++;
			}
			}
		}
	}
	/**
	 * 根据结果集导出EXCEL
	 * @Title: exportExcelForResultSet
	 * @Description: 导出Excel的方法
	 * @author: jesse @ 2015-09-29
	 * @param workbook 
	 * @param sheetNum (sheet的位置，0表示第一个表格中的第一个sheet)
	 * @param sheetTitle  （sheet的名称）
	 * @param headers    （表格的标题）
	 * @param result   SQL查询出的结果集
	 * @param out  （输出流）
	 * @throws Exception
	 */
	@Bizlet("exportExcelForResultSet")
	public static void exportExcelForResultSet(HSSFWorkbook workbook,
			String sheetTitle, String[] headers, ResultSet result) throws Exception {
		// 生成一个表格
		HSSFSheet sheet = workbook.createSheet(sheetTitle);
		// 设置表格默认列宽度为20个字节
		sheet.setDefaultColumnWidth((short) 20);
		// 生成一个样式
		HSSFCellStyle style = workbook.createCellStyle();
		// 设置这些样式
		style.setFillForegroundColor(HSSFColor.PALE_BLUE.index);
		style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);
		style.setBorderTop(HSSFCellStyle.BORDER_THIN);
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		// 生成一个字体
		HSSFFont font = workbook.createFont();
		font.setColor(HSSFColor.BLACK.index);
		font.setFontHeightInPoints((short) 12);
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		// 把字体应用到当前的样式
		style.setFont(font);

		// 指定当单元格内容显示不下时自动换行
		style.setWrapText(true);

		// 产生表格标题行
		HSSFRow row = sheet.createRow(0);
		for (int i = 0; i < headers.length; i++) {
			HSSFCell cell = row.createCell((short) i);
			cell.setCellStyle(style);
			if (headers[i].indexOf(",") == -1) {//判断此字段是否用了业务字典
				HSSFRichTextString text = new HSSFRichTextString(headers[i]);
				cell.setCellValue(text.toString());
			}else{
				String strs[] = headers[i].split(",");
				HSSFRichTextString text = new HSSFRichTextString(strs[0]);
				cell.setCellValue(text.toString());
			}
		}
		// 遍历集合数据，产生数据行
		if (result != null) {
			int index=1;
			while(result.next()){
				int length=headers.length ;
				row = sheet.createRow(index);
				for(int j=1;j<=length;j++){
					if (headers[j-1].indexOf(",") == -1){//判断此字段是否用了业务字典
						HSSFCell cell = row.createCell((short) (j-1));
						String str=result.getString(j);
						if(StringUtils.isBlank(str)){
							str="";
						}
						cell.setCellValue(str.toString());
					}else{
						HSSFCell cell = row.createCell((short) (j-1));
						String str=result.getString(j);
						if("".equals(str)||str == null){
							
						}else{
							String strs[] = headers[j-1].split(",");
							str = com.eos.foundation.eoscommon.BusinessDictUtil.getDictName(strs[1],str);
						}
						if(StringUtils.isBlank(str)){
							str="";
						}
						cell.setCellValue(str.toString());
					}
				}
				index++;
			}
		}
	}
}
